﻿using Groep2TINGHService.Get_Set_Classes;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Groep2TINGHService.DB_Classes
{
    public class ReservationDB
    {
        //Geeft een lijst terug met alle Reservations
        public static List<Reservation> GetAllReservations()
        {
            List<Reservation> lijst = new List<Reservation>();
            SqlConnection connection = ConnectionDB.GetConnection();
            SqlCommand selectcommand = new SqlCommand("select * from Reservation", connection);
            try
            {
                Reservation temp;
                connection.Open();
                SqlDataReader reader = selectcommand.ExecuteReader();
                int Id = reader.GetOrdinal("Id");
                int Number = reader.GetOrdinal("Number");
                int Startdate = reader.GetOrdinal("Startdate");
                int EndDate = reader.GetOrdinal("EndDate");
                int CompanyId = reader.GetOrdinal("CompanyId");
                int LocationId = reader.GetOrdinal("LocationId");
                int CreateDate = reader.GetOrdinal("CreateDate");
                

                while (reader.Read())
                {
                    temp = new Reservation();
                    temp.Id = reader.GetGuid(Id);
                    temp.Number = reader.GetString(Number);
                    temp.StartDate = reader.GetDateTime(Startdate);
                    temp.EndDate = reader.GetDateTime(EndDate);
                    temp.CompanyId = reader.GetGuid(CompanyId);
                    temp.LocationId = reader.GetGuid(LocationId);
                    temp.CreateDate = reader.GetDateTime(CreateDate);
                    lijst.Add(temp);

                }
                reader.Close();
                return lijst;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        //Voegt een nieuwe Reservation toe
        public static bool InsertReservation(Reservation reservation)
        {
            SqlConnection connection = null;
            string insert = "insert into Reservations(Number,Startdate,EndDate,CompanyId,LocationId,CreateDate)" +
                            "Values(@Number,@Startdate,@EndDate,@CompanyId,@LocationId,@CreateDate);";
            SqlCommand insertCommand = new SqlCommand(insert, connection);
            insertCommand.Parameters.AddWithValue("@Number", reservation.Number);
            insertCommand.Parameters.AddWithValue("@Startdate", reservation.StartDate);
            insertCommand.Parameters.AddWithValue("@EndDate", reservation.EndDate);
            insertCommand.Parameters.AddWithValue("@CompanyId", reservation.CompanyId);
            insertCommand.Parameters.AddWithValue("@LocationId", reservation.LocationId);
            insertCommand.Parameters.AddWithValue("@CreateDate", reservation.CreateDate);

            try
            {
                using (connection = ConnectionDB.GetConnection())
                {
                    connection.Open();
                    int count = insertCommand.ExecuteNonQuery();
                    if (count > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

        }

        //Verwijdert een Reservation via Number
        public static bool DeleteReservation(int number)
        {
            SqlConnection connection = ConnectionDB.GetConnection();
            string delete = "DELETE FROM Reservation WHERE Number = @Number";
            SqlCommand deleteCommand = new SqlCommand(delete, connection);
            deleteCommand.Parameters.AddWithValue("@Number", number);
            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        //Wijzigen van een Reservation
        public static bool ModifyReservation(Reservation reservation, int number)
        {
            SqlConnection connection = ConnectionDB.GetConnection();
            string modify = "UPDATE Reservation " +
                            "SET Number=@Number, Startdate=@Startdate, EndDate=@EndDate, CompanyId=@CompanyId, LocationId=@LocationId, CreateDate=@CreateDate " +
                            "WHERE Number = @number";
            SqlCommand modifyCommand = new SqlCommand(modify, connection);
          
            modifyCommand.Parameters.AddWithValue("@Number", number);
            modifyCommand.Parameters.AddWithValue("@Startdate", reservation.StartDate);
            modifyCommand.Parameters.AddWithValue("@EndDate", reservation.EndDate);
            modifyCommand.Parameters.AddWithValue("@CompanyId", reservation.CompanyId);
            modifyCommand.Parameters.AddWithValue("@LocationId", reservation.LocationId);
            modifyCommand.Parameters.AddWithValue("@CreateDate", reservation.CreateDate);

            try
            {
                connection.Open();
                int count = modifyCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }



    }
}